package com.newsoft.frame.codegen.parameter;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.newsoft.frame.codegen.Configuration;

/**
 * To read the database scheme meta data.
 *
 * @author chenw
 */
public class DatabaseSchemaReader {

    private Log logger = LogFactory.getLog(getClass());

    private Connection conn;

    public List<Field> parseTable(String tableName) {
        if (StringUtils.isEmpty(tableName)) {
            return null;
        }

        List<Field> fieldList = new ArrayList<Field>();
        try {
            Connection connection = getDBConnection();
            String querySql = "select * from " + tableName;
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(querySql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int size = rsmd.getColumnCount();
            for (int i = 1; i <= size; i++) {
                Field field = new Field();
                field.setColumnName(rsmd.getColumnName(i));
                field.setFieldName(ParamHelper.getFieldName(field.getColumnName()));
                String fieldType = ParamHelper.sqlType2JavaType(rsmd.getColumnTypeName(i), rsmd.getScale(i));
                field.setFieldType(fieldType);

                fieldList.add(field);
            }
        } catch (SQLException e) {
            logger.error("Error when parse the DB table schema", e);
            closeDBConnection();

            return null;
        }
        return fieldList;
    }

    public List<Field> parseTableInfo(String tableName) {
        if (StringUtils.isEmpty(tableName)) {
            return null;
        }

        List<Field> fieldList = new ArrayList<Field>();
        try {
            Connection connection = getDBConnection();
            String querySql = "select * from " + tableName;
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery(querySql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int size = rsmd.getColumnCount();
            for (int i = 1; i <= size; i++) {
                Field field = new Field();
                field.setColumnName(rsmd.getColumnName(i));
                field.setFieldName(ParamHelper.getFieldName(field.getColumnName()));
                String fieldType = ParamHelper.sqlType2JavaType(rsmd.getColumnTypeName(i), rsmd.getScale(i));
                field.setFieldType(fieldType);

                fieldList.add(field);
            }
        } catch (SQLException e) {
            logger.error("Error when parse the DB table schema", e);
            closeDBConnection();

            return null;
        }
        return fieldList;
    }

    /**
     * 读取封装数据库表字段到
     *
     * @param tableId 数据库表名
     * @return
     * @throws Exception
     */
    public List<Field> parseTableForMysql(String tableId) throws Exception {

        if (StringUtils.isEmpty(tableId)) {
            return null;
        }

        List<Field> fieldList = new ArrayList<Field>();
        try {
            Connection connection = getDBConnection();
            String querySql
                = "select column_name, data_type column_type, column_comment,character_maximum_length column_length,,numeric_precision numericPercision,numeric_scale numericScale from information_schema.columns where table_name = ?";
            PreparedStatement pstmt = connection.prepareStatement(querySql);
            pstmt.setString(1, tableId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String columnName = rs.getString("column_name");
                String columnLength = rs.getString("column_length");
                String columnType = ParamHelper.sqlType2JavaType(rs.getString("column_type"));
                String columnComment = rs.getString("column_comment");
                Field field = new Field();
                field.setColumnName(columnName);
                String numericPercision = rs.getString("numericPercision");
                String numericScale = rs.getString("numericScale");
                field.setFieldLength(columnLength);
                if (StringUtils.isEmpty(columnLength)) {
                    field.setFieldLength(numericPercision);
                    field.setFieldPoint(numericScale);
                }
                field.setColumnType(rs.getString("column_type"));
                field.setFieldName(ParamHelper.getFieldName(field.getColumnName()));
                field.setColumnComment(columnComment);
                field.setFieldType(columnType);
                fieldList.add(field);
            }
        } catch (SQLException e) {
            logger.error("Error when parse the DB table schema", e);
            throw new Exception("Error when parse the DB table schema");
        } finally {
            closeDBConnection();
        }
        return fieldList;
    }

    public void closeDBConnection() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.error("Error when close DB connection", e);
            }
        }
    }

    private Connection getDBConnection() throws SQLException {
        if (conn != null && !conn.isClosed()) {
            return conn;
        }
        Configuration configuration = Configuration.getDefault();
        if (configuration.getJdbcUrl() == null || configuration.getDriverClass() == null
            || configuration.getDbUserName() == null) {
            return null;
        }
        try {
            Class.forName(configuration.getDriverClass())
                .newInstance();
        } catch (Exception e) {
            logger.error("Error when load jdbc driver", e);
        }
        conn = DriverManager.getConnection(configuration.getJdbcUrl(), configuration.getDbUserName(),
            configuration.getDbPassword() == null ? "" : configuration.getDbPassword());

        if (logger.isDebugEnabled()) {
            logger.debug("Succeed to get a DB connection: " + conn);
        }

        return conn;
    }

}
